﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Reflection;
using Common.DbHelper;
using System.Collections;
using Common.DbHelper.HelperException;

namespace Common.DbHelper.BaseHelper
{
    /// <summary>
    /// 作者：赵虎
    /// 时间：2010-1-31
    /// 功能：数据库访问基础类--普通版
    /// </summary>
    public abstract class DataHelp : BaseData, IHelp
    {

        /// <summary>
        /// 构造指定的数据库访问对像.如果要正确使用此类。必须为该类指定数据库访问对像
        /// </summary>
        public DataHelp() { }

        /// <summary>
        /// 构造指定的数据库访问对像
        /// </summary>
        /// <param name="conn">连接对像</param>
        /// <param name="cmd">命令对像</param>
        /// <param name="reader">阅读器对像</param>
        /// <param name="adapter">适配器对像</param>
        public DataHelp(DbConnection conn, DbCommand cmd, DbDataAdapter adapter)
            : base(conn, cmd, adapter)
        {

        }

        /// <summary>
        /// 测试连接
        /// </summary>
        /// <returns>连接是否可用</returns>
        public bool ConnectionTest()
        {
            try
            {
                this.Connection.Open();
                this.Connection.Close();
                return true;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 获取数据库命令。当使用当前的Command对像应正确的调用该方法
        /// </summary>
        /// <param name="cmd">数据库命令</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        protected void InitDbCommand
            (string cmd, CommandType type, params DbParameter[] parameter)
        {
            try
            {
                Command.CommandText = cmd;
                Command.CommandType = type;
                if (Command.Connection == null)
                    Command.Connection = Connection;
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                if (Command.Parameters != null && Command.Parameters.Count > 0)
                    Command.Parameters.Clear();
                if (parameter != null && parameter.Length > 0)
                    Command.Parameters.AddRange(parameter);
            }
            catch (DbException e)
            {
                DbHelpException he = new DbHelpException();
                he.ExceptionType = ExceptionType.BuildCommand;
                he.Exception = e;
                he.ConnectionString = ConnectionString;
                he.Connection = Connection;
                he.Command = Command;

                OnException(he);
            }
        }

        /// <summary>
        /// 获取数据库命令只针对Text类型的命令。当使用当前的Command对像应正确的调用该方法
        /// </summary>
        /// <param name="cmd">数据库命令</param>
        /// <param name="parameter">命令参数</param>
        protected void InitDbCommand
            (string cmd, params DbParameter[] parameter)
        {
            InitDbCommand(cmd, CommandType.Text, parameter);
        }

        /// <summary>
        /// 获取数据库命令只针对Text类型并空参数的命令。
        /// 当使用当前的Command对像应正确的调用该方法
        /// </summary>
        /// <param name="cmd">数据库命令</param>
        protected void InitDbCommand(string cmd)
        {
            InitDbCommand(cmd, CommandType.Text, null);
        }

        /// <summary>
        /// 获取数据库阅读器
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>数据库阅读器。注意关闭阅读器对像</returns>
        public DbDataReader GetDbDataReader
            (string cmd, CommandType type, params DbParameter[] parameter)
        {
            InitDbCommand(cmd, type, parameter);
            DbDataReader reader = null;

            try
            {
                reader = Command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (DbException e)
            {
                DbHelpException he = new DbHelpException();
                he.ExceptionType = ExceptionType.GetDataReader;
                he.Exception = e;
                he.Command = Command;
                he.DataReader = reader;

                OnException(he);
            }

            return reader;
        }

        /// <summary>
        /// 获数据库阅读器.Text命令
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>数据库阅读器 注意关闭阅读器对像</returns>
        public DbDataReader GetDbDataReader
            (string cmd, params DbParameter[] parameter)
        {
            return GetDbDataReader(cmd, CommandType.Text, parameter);
        }

        /// <summary>
        /// 获取数据库阅读器.Text类型,无参数
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <returns>数据库阅读器 注意关闭阅读器对像</returns>
        public DbDataReader GetDbDataReader(string cmd)
        {
            return GetDbDataReader(cmd, CommandType.Text, null);
        }

        /// <summary>
        /// 执行命令并返回行数.如果失败程序自动回滚
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>受影响行数</returns>
        public int ExecuteCommand
            (string cmd, CommandType type, params DbParameter[] parameter)
        {

            InitDbCommand(cmd, type, parameter);
            DbTransaction transaction = Connection.BeginTransaction();
            Command.Transaction = transaction;
            int count = 0;
            try
            {
                count = Command.ExecuteNonQuery();
                transaction.Commit();
            }
            catch (DbException e)
            {
                transaction.Rollback();

                DbHelpException he = new DbHelpException();
                he.ExceptionType = ExceptionType.ExecuteCommand;
                he.Exception = e;
                he.Command = Command;

                OnException(he);

            }
            finally
            {
                Connection.Close();
            }
            return count;
        }



        /// <summary>
        /// 执行命令并返回行数.如果失败程序自动回滚
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>受影响行数</returns>
        public int ExecuteNoTransactionCommand
            (string cmd, CommandType type, params DbParameter[] parameter)
        {
            InitDbCommand(cmd, type, parameter);
            int count = 0;
            try
            {
                count = Command.ExecuteNonQuery();
            }
            catch (DbException e)
            {
                DbHelpException he = new DbHelpException();
                he.ExceptionType = ExceptionType.ExecuteCommand;
                he.Exception = e;
                he.Command = Command;

                OnException(he);
            }
            return count;
        }

        /// <summary>
        /// 执行Text类型命令并返回行数.如果失败程序自动回滚
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>受影响行数</returns>
        public int ExecuteCommand
            (string cmd, params DbParameter[] parameter)
        {
            return ExecuteCommand(cmd, CommandType.Text, parameter);
        }

        /// <summary>
        /// 执行Text类型 无参数 命令并返回行数
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>受影响行数</returns>
        public int ExecuteCommand(string cmd)
        {
            return ExecuteCommand(cmd, CommandType.Text, null);
        }

        /// <summary>
        /// 保存数据并返回当前行
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>当前主键</returns>
        public long SaveData
            (string cmd, CommandType type, params DbParameter[] parameter)
        {
            InitDbCommand(cmd, type, parameter);
            int i = ExecuteCommand(cmd, type, parameter);

            if (i > 0) return ExecuteScalar<long>("SELECT @@IDENTITY");
            return 0;
        }

        /// <summary>
        /// 保存数据并返回当前行.命令类型为Text
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>当前主键</returns>
        public long SaveData
            (string cmd, params DbParameter[] parameter)
        {
            return SaveData(cmd, CommandType.Text, parameter);
        }

        /// <summary>
        /// 执行返回第一行第一列的数据.如果失败程序自动回滚
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>第一行第一列数据</returns>
        public object ExecuteScalar
            (string cmd, CommandType type, DbParameter[] parameter)
        {
            InitDbCommand(cmd, type, parameter);

            object o = null;
            try
            {
                o = Command.ExecuteScalar();
            }
            catch (DbException e)
            {
                DbHelpException he = new DbHelpException();
                he.Exception = e;
                he.Command = Command;
                he.ExceptionType = ExceptionType.ExecuteScalar;

                OnException(he);

            }
            finally
            {
                if (Command.Connection.State == ConnectionState.Open)
                    Command.Connection.Close();
            }

            return o;
        }

        /// <summary>
        /// 执行返回第一行第一列的数据 命令语句为Text
        /// </summary>
        /// <param name="parameter">命令参数</param>
        /// <returns>第一行第一列数据</returns>
        public object ExecuteScalar
            (string cmd, DbParameter[] parameter)
        {
            return ExecuteScalar(cmd, CommandType.Text, parameter);
        }

        /// <summary>
        /// 执行返回第一行第一列的数据 命令语句为Text 无参数
        /// </summary>
        /// <returns>第一行第一列数据</returns>
        public object ExecuteScalar(string cmd)
        {
            return ExecuteScalar(cmd, CommandType.Text, null);
        }

        /// <summary>
        /// 执行返回第一行第一列的数据.如果失败程序自动回滚
        /// </summary>
        /// <typeparam name="T">可以从DB类型转换成基本数据类型的类型如int</typeparam>
        /// <param name="cmd">命令语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>第一行第一列的数据</returns>
        public T ExecuteScalar<T>
            (string cmd, CommandType type, DbParameter[] parameter)
            where T : struct
        {
            object o = null;

            try
            {
                o = ExecuteScalar(cmd, type, parameter);
            }
            catch (DbException e)
            {
                DbHelpException he = new DbHelpException();
                he.Exception = e;
                he.ExceptionType = ExceptionType.ExecuteGenericScalar;

                OnException(he);
            }
            if (o.Equals(null) || o.Equals(DBNull.Value))
                return default(T);
            return (T)o;
        }

        /// <summary>
        /// 执行返回第一行第一列的数据 命令类型为Text
        /// </summary>
        /// <typeparam name="T">可以从DB类型转换成基本数据类型的类型如int</typeparam>
        /// <param name="parameter">命令参数</param>
        /// <returns>第一行第一列的数据</returns>
        public T ExecuteScalar<T>
            (string cmd, DbParameter[] parameter) where T : struct
        {
            return ExecuteScalar<T>(cmd, CommandType.Text, parameter);
        }

        /// <summary>
        /// 执行返回第一行第一列的数据 命令类型为Text 无参数
        /// </summary>
        /// <typeparam name="T">可以从DB类型转换成基本数据类型的类型如int</typeparam>
        /// <returns>第一行第一列的数据</returns>
        public T ExecuteScalar<T>(string cmd) where T : struct
        {
            return ExecuteScalar<T>(cmd, CommandType.Text, null);
        }

        /// <summary>
        /// 实体映射数据实体
        /// </summary>
        /// <typeparam name="T">欲映射的数据实体</typeparam>
        /// <param name="cmd">命令语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="eh">当发生异常时要处理的方法 调用这个方法时先调用OnException</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>实体集合</returns>
        public List<T> FillEntityList<T>
            (string cmd, CommandType type,
            ExceptionHandler eh,
            params DbParameter[] parameter) where T : class, new()
        {
            PropertyInfo[] Properties = GetProperties<T>();
            List<T> list = new List<T>();
            DbDataReader reader = GetDbDataReader(cmd, type, parameter);
            try
            {
                while (reader.Read())
                {
                    T entity = new T();

                    foreach (PropertyInfo prot in Properties)
                    {
                        object o = reader[prot.Name];
                        if (o.Equals(DBNull.Value)) continue;
                        prot.SetValue(entity, o, null);
                    }

                    list.Add(entity);
                }
            }
            catch (DbException e)
            {
                DbHelpException oe = new DbHelpException();
                oe.ExceptionType = ExceptionType.ExecuteGenericEntity;
                oe.Exception = e;
                oe.DataReader = reader;
                OnException(oe);

                if (eh != null) eh(oe);
            }
            finally
            {
                if (!reader.IsClosed) reader.Close();
            }

            return list;
        }

        /// <summary>
        /// 实体映射数据实体,命令类型Text
        /// </summary>
        /// <typeparam name="T">欲映射的数据实体</typeparam>
        /// <param name="cmd">命令语句</param>
        /// <param name="eh">当发生异常时要处理的方法 调用这个方法时先调用OnException</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>实体集合</returns>
        public List<T> FillEntityList<T>
            (string cmd, ExceptionHandler eh,
            params DbParameter[] parameter) where T : class, new()
        {
            return FillEntityList<T>(cmd, CommandType.Text, eh, parameter);
        }

        /// <summary>
        /// 实体映射数据实体,命令类型Text,系统自动处理异常
        /// </summary>
        /// <typeparam name="T">欲映射的数据实体</typeparam>
        /// <param name="cmd">命令语句</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>实体集合</returns>
        public List<T> FillEntityList<T>(string cmd, params DbParameter[] parameter)
            where T : class, new()
        {
            return FillEntityList<T>(cmd, CommandType.Text, null, parameter);
        }

        /// <summary>
        /// 实体映射数据实体,命令类型Text,系统自动处理异常,无参数
        /// </summary>
        /// <typeparam name="T">欲映射的数据实体</typeparam>
        /// <param name="cmd">命令语句</param>
        /// <returns>实体集合</returns>
        public List<T> FillEntityList<T>(string cmd) where T : class, new()
        {
            return FillEntityList<T>(cmd, CommandType.Text, null, null);
        }

        /// <summary>
        /// 实体映射数据实体
        /// </summary>
        /// <typeparam name="T">欲映射的数据实体</typeparam>
        /// <param name="cmd">命令语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>实体</returns>
        public T FindByEntity<T>(string cmd, CommandType type, params DbParameter[] parameter)
            where T : class, new()
        {
            List<T> list = FillEntityList<T>(cmd, type, null, parameter);
            if (list != null && list.Count > 0) return list[0];
            return null;
        }

        /// <summary>
        /// 实体映射数据实体,命令类型Text
        /// </summary>
        /// <typeparam name="T">欲映射的数据实体</typeparam>
        /// <param name="cmd">命令语句</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>实体</returns>
        public T FindByEntity<T>(string cmd, params DbParameter[] parameter)
            where T : class, new()
        {
            return FindByEntity<T>(cmd, CommandType.Text, parameter);
        }

        /// <summary>
        /// 利用适配器填充DataTable
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        /// <param name="eh">错误处理</param>
        /// <returns>DataTable</returns>
        public DataTable FillDataTable
             (string cmd, CommandType type, ExceptionHandler eh, params DbParameter[] parameter)
        {
            InitDbCommand(cmd, type, parameter);
            DataTable table = new DataTable();
            try
            {
                Adapter.SelectCommand = Command;
                Adapter.Fill(table);
            }
            catch (DbException e)
            {
                DbHelpException oe = new DbHelpException();
                oe.Exception = e;
                oe.Adapter = Adapter;
                OnException(oe);

                if (eh != null) eh(oe);
            }
            finally
            {
                if (Connection.State == ConnectionState.Open)
                    Connection.Close();
            }
            return table;
        }

        /// <summary>
        /// 利用适配器填充DataTable,Text命令
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <param name="parameter">命令参数</param>
        /// <param name="eh">错误处理</param>
        /// <returns>DataTable</returns>
        public DataTable FillDataTable
             (string cmd, ExceptionHandler eh, DbParameter[] parameter)
        {
            return FillDataTable(cmd, CommandType.Text, eh, parameter);
        }

        /// <summary>
        /// 利用适配器填充DataTable,Text命令,无错误处理
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>DataTable</returns>
        public DataTable FillDataTable(string cmd, DbParameter[] parameter)
        {
            return FillDataTable(cmd, CommandType.Text, null, parameter);
        }

        /// <summary>
        /// 利用适配器填充DataTable,Text命令,无错误处理,无命令参数
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <returns>DataTable</returns>
        public DataTable FillDataTable(string cmd)
        {
            return FillDataTable(cmd, CommandType.Text, null, null);
        }

        /// <summary>
        /// 取类型所有属性
        /// </summary>
        /// <typeparam name="T">类 无参公共构造</typeparam>
        /// <returns>类的属性信息</returns>
        private PropertyInfo[] GetProperties<T>() where T : class, new()
        {
            Type type = typeof(T);
            PropertyInfo[] protys = type.GetProperties();
            return protys;
        }

        /// <summary>
        /// 当连接 数据库发生异常时
        /// </summary>
        /// <param name="e">当瓣异常对像</param>
        protected abstract void OnException(DbHelpException e);

        /// <summary>
        /// Hashtable映射数据实体
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="eh">当发生异常时要处理的方法 调用这个方法时先调用OnException</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>实体Hashtable集合</returns>
        public List<Hashtable> FillHashtableList(string cmd, CommandType type, ExceptionHandler eh, params DbParameter[] parameter)
        {
            DbDataReader reader = GetDbDataReader(cmd, type, parameter);
            List<Hashtable> list = new List<Hashtable>();
            try
            {
                while (reader.Read())
                {
                    Hashtable ht = new Hashtable();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        ht.Add(reader.GetName(i), reader[i]);
                    }
                    list.Add(ht);
                }
            }
            catch (DbException e)
            {
                DbHelpException oe = new DbHelpException();
                oe.ExceptionType = ExceptionType.ExecuteGenericEntity;
                oe.Exception = e;
                oe.DataReader = reader;
                OnException(oe);
                if (eh != null) eh(oe);
            }
            finally
            {
                if (!reader.IsClosed) reader.Close();
            }
            return list;
        }

        /// <summary>
        /// Hashtable映射数据实体
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>实体Hashtable集合</returns>
        public Hashtable FindHashtable(string cmd, CommandType type, params DbParameter[] parameter)
        {
            DbDataReader reader = GetDbDataReader(cmd, type, parameter);
            Hashtable ht = null;
            try
            {
                if (reader.Read())
                {
                    ht = new Hashtable();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        ht.Add(reader.GetName(i), reader[i]);
                    }
                }
            }
            catch (DbException e)
            {
                throw e;
            }
            finally
            {
                if (!reader.IsClosed) reader.Close();
            }
            return ht;
        }

        /// <summary>
        /// Hashtable映射数据实体 Text命令类型
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>实体Hashtable集合</returns>
        public Hashtable FindHashtable(string cmd, params DbParameter[] parameter)
        {
            return FindHashtable(cmd, CommandType.Text, parameter);
        }

        /// <summary>
        /// Hashtable映射数据实体 Text命令类型
        /// </summary>
        /// <param name="cmd">命令语句</param>
        /// <param name="type">命令类型</param>
        /// <param name="eh">当发生异常时要处理的方法 调用这个方法时先调用OnException</param>
        /// <param name="parameter">命令参数</param>
        /// <returns>实体Hashtable集合</returns>
        public List<Hashtable> FillHashtableList(string cmd, params DbParameter[] parameter)
        {
            return FillHashtableList(cmd, CommandType.Text, null, parameter);
        }


    }
}
